/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.telsoft.security.data;

import com.telsoft.security.entity.SEC_ETT_User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import javax.naming.spi.DirStateFactory;

/**
 *
 * @author nguyenthang
 */
public class SEC_DATA_User {

    private Connection connect = null;

    public SEC_DATA_User() {
    }

    public SEC_ETT_User getPassWordbyUserName(String username) {
        SEC_ETT_User returnValue = new SEC_ETT_User();
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        ResultSet rs;
        PreparedStatement stmt;
        try {
            connect = connectDB.dbConnect();
            String strSQL = "SELECT USER_ID, USER_NAME, FULL_NAME, STATUS, EMAIL, PASSWORD FROM SEC_USER WHERE USER_NAME='"
                    + username + "'";
            stmt = connect.prepareStatement(strSQL);
            rs = stmt.executeQuery();
            while (rs.next()) {
                returnValue.setUSER_ID(rs.getInt("USER_ID"));
                returnValue.setUSER_NAME(rs.getString("USER_NAME"));
                returnValue.setFULL_NAME(rs.getString("FULL_NAME"));
                returnValue.setSTATUS(rs.getInt("STATUS"));
                returnValue.setEMAIL(rs.getString("EMAIL"));
                returnValue.setPASSWORD(rs.getString("PASSWORD"));
            }
            connectDB.dbClose(connect, stmt, rs);
        } catch (Exception ex) {
            System.out.print(ex.toString());
        }
        return returnValue;
    }

    public List<SEC_ETT_User> getAllUser() {
        List<SEC_ETT_User> returnValue = new ArrayList<SEC_ETT_User>();
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        ResultSet rs;
        SEC_ETT_User user;
        try {
            connect = connectDB.dbConnect();
            String query = "SELECT * FROM SEC_USER ORDER BY USER_ID ASC";
            stmt = connect.prepareStatement(query);
            rs = stmt.executeQuery();
            while (rs.next()) {
                user = new SEC_ETT_User();
                user.setUSER_ID(rs.getInt("USER_ID"));
                user.setUSER_NAME(rs.getString("USER_NAME"));
                user.setFULL_NAME(rs.getString("FULL_NAME"));
                user.setSTATUS(rs.getInt("STATUS"));
                user.setEMAIL(rs.getString("EMAIL"));
                user.setPASSWORD(rs.getString("PASSWORD"));

                returnValue.add(user);
            }
            connectDB.dbClose(connect, stmt, rs);
        } catch (Exception ex) {
            ex.toString();
        }
        return returnValue;
    }

    public void addUser(SEC_ETT_User newUser) {
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        try {
            connect = connectDB.dbConnect();
            String query = "INSERT INTO SEC_USER(USER_NAME, FULL_NAME, STATUS, EMAIL, PASSWORD) VALUES (?,?,?,?,?)";
            stmt = connect.prepareStatement(query);
            stmt.setString(1, newUser.getUSER_NAME());
            stmt.setString(2, newUser.getFULL_NAME());
            stmt.setInt(3, newUser.getSTATUS());
            stmt.setString(4, newUser.getEMAIL());
            stmt.setString(5, newUser.getPASSWORD());
            stmt.execute();
            connectDB.dbClose(connect);
            connectDB.dbClose(stmt);
        } catch (Exception ex) {
            ex.toString();
        }
    }

    public void updateUser(int CurUserID, SEC_ETT_User newUser) {
        SEC_DAOHelper connnectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        try {
            connect = connnectDB.dbConnect();
            String query = "UPDATE SEC_USER SET USER_ID = ?, USER_NAME= ?, FULL_NAME= ?, STATUS= ?, EMAIL= ?, PASSWORD= ? WHERE USER_ID=?";
            stmt = connect.prepareStatement(query);
            stmt.setInt(1, CurUserID);
            stmt.setString(2, newUser.getUSER_NAME());
            stmt.setString(3, newUser.getFULL_NAME());
            stmt.setInt(4, newUser.getSTATUS());
            stmt.setString(5, newUser.getEMAIL());
            stmt.setString(6, newUser.getPASSWORD());
            stmt.setInt(7, CurUserID);
            stmt.execute();
            connnectDB.dbClose(connect);
            connnectDB.dbClose(stmt);
        } catch (Exception ex) {
            ex.toString();
        }
    }

    public void deleteUser(int UserID) {
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        try {
            connect = connectDB.dbConnect();
            String query = "DELETE FROM SEC_USER WHERE USER_ID=?";
            stmt = connect.prepareStatement(query);
            stmt.setInt(1, UserID);
            stmt.execute();
            connectDB.dbClose(connect);
            connectDB.dbClose(stmt);
        } catch (Exception ex) {
            ex.toString();
        }
    }

    public SEC_ETT_User getUserByID(int userID) {
        SEC_ETT_User user = new SEC_ETT_User();
        SEC_DAOHelper connectDB = new SEC_DAOHelper();
        PreparedStatement stmt;
        ResultSet rs;
        try {
            connect = connectDB.dbConnect();
            String query = "SELECT * FROM SEC_USER WHERE USER_ID = '" + userID + "'";
            stmt = connect.prepareStatement(query);
            rs = stmt.executeQuery();
            while (rs.next()) {
                user.setUSER_ID(rs.getInt("USER_ID"));
                user.setUSER_NAME(rs.getString("USER_NAME"));
                user.setFULL_NAME(rs.getString("FULL_NAME"));
                user.setSTATUS(rs.getInt("STATUS"));
                user.setEMAIL(rs.getString("EMAIL"));
                user.setPASSWORD(rs.getString("PASSWORD"));
            }
            connectDB.dbClose(connect, stmt, rs);
        } catch (Exception ex) {
            ex.toString();
        }
        return user;
    }

    public SEC_ETT_User getUserByUserName(String UserName) {
        SEC_ETT_User user = new SEC_ETT_User();
        SEC_DAOHelper connectBD = new SEC_DAOHelper();
        PreparedStatement stmt;
        ResultSet rs;
        try {
            connect = connectBD.dbConnect();
            String query = "SELECT * FROM SEC_USER WHERE USER_NAME = ?";
            stmt = connect.prepareStatement(query);
            stmt.setString(1, UserName);
            rs = stmt.executeQuery();
            while (rs.next()) {
                user.setUSER_ID(rs.getInt("USER_ID"));
                user.setUSER_NAME(rs.getString("USER_NAME"));
                user.setFULL_NAME(rs.getString("FULL_NAME"));
                user.setSTATUS(rs.getInt("STATUS"));
                user.setEMAIL(rs.getString("EMAIL"));
                user.setPASSWORD(rs.getString("PASSWORD"));
                connectBD.dbClose(connect, stmt, rs);
            }
        } catch (Exception ex) {
            ex.toString();
        }

        return user;
    }
}
